--1--
create table student(
    sno integer primary key,
    name varchar(20),
    sex varchar(4),
    age integer,
    clszz varchar(20)
);

create table teacher(
    tno integer primary key,
    name varchar(20),
    sex varchar(4),
    age integer
);

create table lesson(
    lno integer primary key,
    name varchar(20),
    tno integer,
    foreign key(tno) references teacher(tno)
);

create table score(
    lno integer,
    sno integer,
    score float,
    foreign key(lno) references lesson(lno),
    foreign key(sno) references student(sno)
);

--2.1 添加主键--
alter table student add primary key(sno);
alter table teacher add primary key(tno);
alter table lesson add primary key(lno);


--2.2 添加非空约束--
alter table student modify name not null;
alter table teacher modify name not null;
alter table lesson modify name not null;

--2.3 添加数据检查约束--
alter table student
add constraint ck1 check(sex='男' or sex='女');

alter table student
add constraint ck2 check(age>0 and age<99);

alter table teacher
add constraint ck3 check(sex='男' or sex='女');

alter table teacher
add constraint ck4 check(age>0 and age<99);

alter table score
add constraint ck5 check(score>=0 and score<=100);

--2.5 添加外键--
alter table lesson
add constraint ck7 foreign key(tno) references teacher(tno);

alter table score
add constraint ck8 foreign key(lno) references lesson(lno);

alter table score
add constraint ck9 foreign key(sno) references student(sno);

--3 添加序列--
create sequence stu_s
increment by 1
start with 2001;

create sequence tea_s
increment by 10
start with 10;

create sequence lesson_s
increment by 1
start with 0
MINVALUE 0;

--4 使用序列插入数据--
insert into student
values
(stu_s.nextval, 'lee', '男', 21, '软件二班');
insert into student
values
(stu_s.nextval, 'wang', '男', 22, '软件一班');
insert into student
values
(stu_s.nextval, 'zhang', '女', 20, '软件二班');
insert into student
values
(stu_s.nextval, 'zhao', '男', 22, '软件一班');
insert into student
values
(stu_s.nextval, 'zheng', '男', 21, '软件二班');

insert into teacher
values
(tea_s.nextval, 'lv', '女', 45);
insert into teacher
values
(tea_s.nextval, 'sun', '女', 33);

insert into lesson
values
(lesson_s.nextval, '数据库原理', 20);
insert into lesson
values
(lesson_s.nextval, '编译原理', 30);

insert into score
values
(1,2007,90);
insert into score
values
(1,2008,80);
insert into score
values
(1,2009,78);
insert into score
values
(2,2010,99);
insert into score
values
(2,2011,95);

--5 视图--
/* 权限不足如下
PS C:\WINDOWS\system32> sqlplus;

SQL*Plus: Release 11.2.0.1.0 Production on 星期四 11月 5 14:01:51 2020

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

请输入用户名:  system
输入口令:

连接到:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> grant create any view to scott;

授权成功。
*/
create or replace view view_1
as
select student.sno, student.name sname, lesson.lno, lesson.name lname, teacher.name tname, score
from student, lesson, teacher, score
where score.lno = lesson.lno and score.sno = student.sno and lesson.tno = teacher.tno;

select avg(score)
from view_1,student
where lname='数据库原理' and view_1.sno = student.sno and student.sex='女';

select count(*)
from view_1
where score>=60
group by lno;

drop view view_1;

--6--
drop table score;
drop table lesson;
drop table teacher;
drop table student;

show recyclebin;
flashback table student to before drop;
flashback table teacher to before drop;

purge table lesson;
purge table score;

drop table student purge;
drop table teacher purge;